#!/usr/bin/python

# modifies users table:
#	1. userID is now SERIAL
#	2. name is now UNIQUE
# 	3. old users table dropped
#	4. new user table added to db

import sqlite3

if __name__ == "__main__":

    db = sqlite3.connect("pappa-bot copy.db")
    c = db.cursor()
        
    # try:
    #     c.execute('''DROP TABLE users''')
    # except:
    #     pass
    # 
    # c.execute('''CREATE TABLE users (
    #   userID INTEGER,
    #   name TEXT UNIQUE,
    #   PRIMARY KEY (userID)
    #   );''')

    #c.execute('''INSERT INTO cabinets(userID,ingredientID) VALUES(?,?)''', (1, 316))
    #c.execute('''INSERT INTO cabinets(userID,ingredientID) VALUES(?,?)''', (1, 445))
    
    # we add categories (categoryID,name) manually by looking over drink names
    # then, for each category (hard-coded one at a time by ID) we find all drinks in that
    # category, and add the drinkID/categoryID to the drink_categories table
    
    # add the tables
    
    # c.execute('''CREATE TABLE categories (
    #     categoryID INTEGER SERIAL,
    #     name TEXT,
    #     PRIMARY KEY (categoryID)
    #     );''');
    #     
    # c.execute('''CREATE TABLE drink_categories (
    #     drinkID INTEGER,
    #     categoryID INTEGER,
    #     FOREIGN KEY (drinkID) REFERENCES drinks,
    #     FOREIGN KEY (categoryID) REFERENCES categories,
    #     PRIMARY KEY (drinkID,categoryID)
    #     );''');
    
    # add each keyword
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('1','fizz');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('2','sour');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('3','colada');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('4','punch');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('5','daiquiri');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('6','tea');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('7','coffee');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('8','martini');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('9','lemonade');''');
    # c.execute('''INSERT INTO categories (categoryID,name) VALUES ('10','swizzle');''');
    
    # get all drink IDs for the keyword
    c.execute('SELECT drinkID FROM drinks WHERE name LIKE \'%swizzle%\';')
    drinkIDs = c.fetchall()
    print drinkIDs
    
    # for each drink, add an entry to drink_categories
    for drinkID in drinkIDs:
        c.execute('INSERT INTO drink_categories(categoryID,drinkID) VALUES(?,?)', (10, drinkID[0]))
        
    db.commit()
    c.close()

    exit(0)
